//    Copyright (C) Kherty.  All rights reserved.
using OpenLS.Spreadsheet.Extensibility;
using OpenLS.Spreadsheet.StandardAddIn.Database;

namespace OpenLS.Spreadsheet.StandardAddIn
{
    [AddInFunctions]
    [AllowCellToRangeConversion]
    public class DatabaseFunctions
    {
        public static object DAverage([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.Average(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object DCount([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.CountImp(db.QueryObjectsAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DCountA([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return db.QueryObjectsAsArray(field, criteria, worksheet.Workbook).Length;
        }

        public static object
            DGet([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            object[] o = db.QueryObjectsAsArray(field, criteria, worksheet.Workbook);
            if (o.Length != 1)
                return Errors.NumError;
            return o[0];
        }

        public static object
            DMax([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.Max(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DMin([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.Min(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DProduct([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return MathFunctions.Product(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DStDev([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.StDev(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DStDevP([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.StDevP(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DSum([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return MathFunctions.Sum(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DVar([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.Var(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }

        public static object
            DVarP([WorksheetContext]IWorksheet worksheet, IRange database, string field, IRange criteria)
        {
            DataSource db = new DataSource(database);
            if (db.IsEmpty)
                return Errors.ValueError;
            return StatisticalFunctions.VarP(db.QueryDoublesAsArray(field, criteria, worksheet.Workbook));
        }
    }
}